from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *
import Ui_lapPelanggan
import utility

class LapPelanggan(QDialog, Ui_lapPelanggan.Ui_LapPelangganDlg):
  def __init__(self, dbConn, dataGlob,  parent=None):
        super(LapPelanggan, self).__init__(parent)
        self.setupUi(self)
        self.initMenu()
        self.tgl_cb.setCheckState(Qt.Checked)
        self.tgl_aw.setDate(QDate.currentDate())
        self.tgl_ak.setDate(QDate.currentDate())
        self.noMmc_cb.setCheckState(Qt.Checked)
        self.noMmc_le.setText("000001")
        self.db = dbConn
        self.dataGlob = dataGlob
        self.util = utility.Utility(self.db)
        
        self.plgModel = QSqlQueryModel(self)
        self.plgModel.setHeaderData(0, Qt.Horizontal, QVariant("No MMC"))
        self.plgModel.setHeaderData(2, Qt.Horizontal, QVariant("Nama"))
        self.plgModel.setHeaderData(4, Qt.Horizontal, QVariant("Total Trans"))
        self.plgModel.setHeaderData(5, Qt.Horizontal, QVariant("Total CIB"))
        self.plgModel.setHeaderData(6, Qt.Horizontal, QVariant("Total Non CIB"))
        self.plgModel.setHeaderData(7, Qt.Horizontal, QVariant("Total Poin"))
        self.plgModel.setHeaderData(8, Qt.Horizontal, QVariant("Undian"))
        self.plgModel.setHeaderData(9, Qt.Horizontal, QVariant("Sisa Poin"))
        self.plgModel.setHeaderData(10, Qt.Horizontal, QVariant("Emas CIB"))
        self.plgModel.setHeaderData(11, Qt.Horizontal, QVariant("Umum CIB"))
        self.plgModel.setHeaderData(12, Qt.Horizontal, QVariant("Emas Non CIB"))
        self.plgModel.setHeaderData(13, Qt.Horizontal, QVariant("Umum Non CIB"))
        
        self.master_tbv.setModel(self.plgModel)
        self.master_tbv.setItemDelegate(MasterDelegate(self))
        self.master_tbv.setSelectionMode(QTableView.SingleSelection)
        self.master_tbv.setSelectionBehavior(QTableView.SelectRows)
        self.connect(self.master_tbv.selectionModel(), SIGNAL("currentRowChanged(QModelIndex,QModelIndex)"), self.rowChanged)
        
        self.transModel = QSqlQueryModel(self)
        self.transModel.setHeaderData(1, Qt.Horizontal, QVariant("No Trans"))
        self.transModel.setHeaderData(2, Qt.Horizontal, QVariant("Tgl Trans"))
        self.transModel.setHeaderData(3, Qt.Horizontal, QVariant("Total (Rp)"))
        self.transModel.setHeaderData(4, Qt.Horizontal, QVariant("Poin"))
        self.transModel.setHeaderData(5, Qt.Horizontal, QVariant("Tipe Kartu"))
        self.transModel.setHeaderData(6, Qt.Horizontal, QVariant("Jenis Toko"))
        
        self.trans_tbv.setModel(self.transModel)
        self.trans_tbv.setItemDelegate(DetailDelegate(self))
        self.trans_tbv.setSelectionMode(QTableView.SingleSelection)
        self.trans_tbv.setSelectionBehavior(QTableView.SelectRows)
        
  @pyqtSignature("")
  def on_noMmc_le_editingFinished(self):
      if self.noMmc_le.text() != "":
          self.noMmc_cb.setCheckState(Qt.Checked)
      else:
          self.noMmc_cb.setCheckState(Qt.Unchecked)
        
  @pyqtSignature("")
  def on_nama_le_editingFinished(self):
      if self.nama_le.text() != "":
          self.nama_cb.setCheckState(Qt.Checked)
      else:
          self.nama_cb.setCheckState(Qt.Unchecked)
          
  @pyqtSignature("")
  def on_alamat_le_editingFinished(self):
      if self.alamat_le.text() != "":
          self.alamat_cb.setCheckState(Qt.Checked)
      else:
          self.alamat_cb.setCheckState(Qt.Unchecked)
          
  @pyqtSignature("")
  def on_tgl_aw_editingFinished(self):
      self.tgl_cb.setCheckState(Qt.Checked)
          
  @pyqtSignature("")
  def on_tgl_ak_editingFinished(self):
      self.tgl_cb.setCheckState(Qt.Checked)
          
  def initMenu(self):
      self.retrieveAct = QAction(self.tr("Retrieve"), self)
      self.connect(self.retrieveAct, SIGNAL("triggered()"), self.retrieveData)
      self.sortAct = QAction(self.tr("Sort"), self)
      self.printAct = QAction(self.tr("Print Preview"), self)

  def retrieveData(self):
      qStr = "SELECT p.no_kartu, p.kode_pelanggan,  p.nama  , p.id_pelanggan, "\
      "(select sum(total_rp) from transaksi where id_pelanggan = p.id_pelanggan) as total_trans, "\
      "(select sum(total_rp) from transaksi where id_pelanggan = p.id_pelanggan and cc_tipe = 'CIB') as trans_cib,"\
      "0 as trans_noncib, "\
      "(select sum(poin) from transaksi where id_pelanggan = p.id_pelanggan) as total_poin, "\
      "(select sum(keluar) from poin_trans where tipe_trans = 'UND' and id_pelanggan = p.id_pelanggan) as jum_undian, "\
      "(select sum(masuk - keluar) from poin_trans where id_pelanggan = p.id_pelanggan) as sisa_poin, "\
      "(select sum(total_rp) from transaksi where id_pelanggan = p.id_pelanggan and spec_store = 'EMS'and cc_tipe <> 'CIB') as trans_emas, "\
      "(select sum(total_rp) from transaksi where id_pelanggan = p.id_pelanggan and (spec_store <> 'EMS' or spec_store is null) and cc_tipe <> 'CIB') as trans_nonemas, "\
      "(select sum(total_rp) from transaksi where id_pelanggan = p.id_pelanggan and spec_store = 'EMS' and cc_tipe = 'CIB') as trans_emascb, "\
      "(select sum(total_rp) from transaksi where id_pelanggan = p.id_pelanggan and (spec_store <> 'EMS' or spec_store is null) and cc_tipe = 'CIB') as trans_nonemascb "\
      "FROM pelanggan   p"
            
      filt = ""
      urut = " order by p.nama "
      if self.noMmc_cb.checkState() == Qt.Checked:
          filt = filt + " p.no_kartu like '"+self.noMmc_le.text()+"%' and "
      if self.nama_cb.checkState() == Qt.Checked:
          filt = filt + " p.nama like '"+self.nama_le.text()+"%' and "
      if self.alamat_cb.checkState() == Qt.Checked:
          filt = filt + " p.alamat1 like '"+self.alamat_le.text()+"%' and "
#      if self.tgl_cb.checkState() == Qt.Checked:
#          filt = filt + " t.tgl_trans between '"+self.tgl_aw.date().toString("yyyy-MM-dd")+"' and '"+self.tgl_ak.date().toString("yyyy-MM-dd")+"' and "
          
      if filt != "":
          filt = " where "+filt.left(len(filt)-4)
          
      qStr = qStr + filt + urut
      self.plgModel.setQuery(qStr, self.db)
      self.master_tbv.setColumnHidden(1, True)
      self.master_tbv.setColumnHidden(3, True)
      self.master_tbv.setColumnHidden(6, True)
      self.master_tbv.setColumnWidth(0, 65)
      self.master_tbv.setColumnWidth(2, 150)
      self.master_tbv.resizeRowsToContents()
      self.hitungTotal()
    
  def sortData(self):
       qStr = "select id_pelanggan, kode_pelanggan, nama, no_kartu, alamat1, kota1 from pelanggan"
       self.plgModel.setQuery(qStr, self.db)

  def printData(self):
       qStr = "select id_pelanggan, kode_pelanggan, nama, no_kartu, alamat1, kota1 from pelanggan"
       self.plgModel.setQuery(qStr, self.db)

  def contextMenuEvent(self, event):
        menu = QMenu(self)
        menu.addAction(self.retrieveAct)
        menu.addAction(self.sortAct)
        menu.addSeparator()
        menu.addAction(self.printAct)
        menu.exec_(event.globalPos())

  def rowChanged(self):
      index = self.master_tbv.currentIndex()
      if index.isValid():
          record = self.plgModel.record(index.row())
          id = record.value("id_pelanggan").toString()          
          #++++++++++++ update tab transaksi ++++++++++++++++
          qStr = "select id_transaksi, no_trans, tgl_trans, total_rp, poin, cc_tipe, spec_store from transaksi where id_pelanggan = '"+id+"'"
          filt = ""
          urut = " order by tgl_trans "
          if self.tgl_cb.checkState() == Qt.Checked:
              filt = filt + " tgl_trans between '"+self.tgl_aw.date().toString("yyyy-MM-dd")+"' and '"+self.tgl_ak.date().toString("yyyy-MM-dd")+"' and "
              
          if filt != "":
              filt = " where "+filt.left(len(filt)-4)
              
          qStr = qStr + filt + urut
          self.transModel.setQuery(qStr, self.db)
          self.trans_tbv.setColumnHidden(0, True)
          self.trans_tbv.setColumnWidth(1, 250)
          self.trans_tbv.setColumnWidth(2, 100)
          self.trans_tbv.setColumnWidth(3, 200)
          self.trans_tbv.setColumnWidth(4, 100)
          self.trans_tbv.resizeRowsToContents()
          
          #++++++++++++ update tab pelanggan ++++++++++++++++
          query = QSqlQuery(self.db)
          qStr = "select id_pelanggan, kode_pelanggan, nama, alamat1, kota1, kodepos1, telp1, fax1, alamat2, kota2, kodepos2, telp2, fax2, hp, email, no_kartu, no_ktp, npwp, ket, audit_user, tgl_daftar, audit_tgl, jk, tgl_lahir, bidang, pekerjaan from pelanggan where id_pelanggan = '"+id+"'"
          query.exec_(qStr)
          query.next()
          self.id_le.setText(unicode(query.value(0).toString()))
          self.kode_le.setText(unicode(query.value(1).toString()))
          self.nama2_le.setText(unicode(query.value(2).toString()))
          self.alamat2_le.setText(unicode(query.value(3).toString()))
          self.kota_le.setText(unicode(query.value(4).toString()))
          self.kodepos_le.setText(unicode(query.value(5).toString()))
          self.telp_le.setText(unicode(query.value(6).toString()))
          self.fax_le.setText(unicode(query.value(7).toString()))
          self.alamat3_le.setText(unicode(query.value(8).toString()))
          self.kota2_le.setText(unicode(query.value(9).toString()))
          self.kodepos2_le.setText(unicode(query.value(10).toString()))
          self.telp2_le.setText(unicode(query.value(11).toString()))
          self.fax2_le.setText(unicode(query.value(12).toString()))
          self.hp_le.setText(unicode(query.value(13).toString()))
          self.email_le.setText(unicode(query.value(14).toString()))
          self.noMmc2_le.setText(unicode(query.value(15).toString()))
          self.ktp_le.setText(unicode(query.value(16).toString()))
          self.npwp_le.setText(unicode(query.value(17).toString()))
          self.auditUser_le.setText(unicode(query.value(19).toString()))
          self.tglDaftar_de.setDate(query.value(20).toDate())
          self.auditWaktu_le.setText(query.value(21).toDateTime().toString("dd-MM-yyyy hh:mm"))
          idx = 0
          if unicode(query.value(22).toString()) == "P":
              idx = 1
          self.jk_cmb.setCurrentIndex(idx)
          self.tglLahir_de.setDate(query.value(23).toDate())
          self.bidang_le.setText(unicode(query.value(24).toString()))
          self.pekj_le.setText(unicode(query.value(25).toString()))
          #++++++++++++ update tab undian ++++++++++++++++
          #++++++++++++ update tab hadiah ++++++++++++++++
          
  def hitungTotal(self):
      totBrs = self.plgModel.rowCount()
      rpTot = 0
      poinTot = 0
      sisaTot = 0
      for i in range(totBrs):
          record = self.plgModel.record(i)
          rpTot += record.value(4).toDouble()[0]
          poinTot += record.value(7).toDouble()[0]
          sisaTot += record.value(9).toDouble()[0]
      self.totalRp_le.setText(str(rpTot))
      self.totalPoin_le.setText(str(poinTot))
      self.sisaPoin_le.setText(str(sisaTot))

class MasterDelegate(QSqlRelationalDelegate):
    def __init__(self, parent=None):
        super(MasterDelegate, self).__init__(parent)
        
    def paint(self, painter, option, index):
        myoption = QStyleOptionViewItem(option)
        if index.column() != 0 and index.column() != 2:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        QSqlRelationalDelegate.paint(self, painter, myoption, index)
        
class DetailDelegate(QSqlRelationalDelegate):
    def __init__(self, parent=None):
        super(DetailDelegate, self).__init__(parent)
        
    def paint(self, painter, option, index):
        myoption = QStyleOptionViewItem(option)
        if index.column() == 3:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        QSqlRelationalDelegate.paint(self, painter, myoption, index)
        
if __name__ == "__main__":
    import sys
    app = QApplication(sys.argv)
    form = LapPelanggan()
    form.show()
    app.exec_()
